﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using HenIT.Data.SqlClient;
using System.Data.SqlClient;
using System.Data;

namespace EventScavenger
{
    public partial class EventScavengerDAL : GenericSQLServerDAL
    {
        public EventScavengerDAL(): base()
        {
            maxCount = 50;
            untilDate = DateTime.Now;
        }

        #region Properties
        //****** Filters ******
        private int maxCount;
        public int MaxCount
        {
            get { return maxCount; }
            set { maxCount = value; }
        }
        private string messageFilter = "";
        public string MessageFilter
        {
            get { return messageFilter; }
            set { messageFilter = value; }
        }

        private DateTime fromDate;
        public DateTime FromDate
        {
            get { return fromDate; }
            set { fromDate = value; }
        }
        private DateTime untilDate;
        public DateTime UntilDate
        {
            get { return untilDate; }
            set { untilDate = value; }
        }
        private EventLogEntryTypes eventLogEntryType = EventLogEntryTypes.All;
        public EventLogEntryTypes EventLogEntryType
        {
            get { return eventLogEntryType; }
            set { eventLogEntryType = value; }
        }

        #region Event IDs
        private bool viewAllEventIDs = true;
        public bool ViewAllEventIDs
        {
            get { return viewAllEventIDs; }
            set { viewAllEventIDs = value; }
        }
        private List<int> eventIdsVisible = new List<int>();
        public List<int> EventIdsVisible
        {
            get { return eventIdsVisible; }
            set { eventIdsVisible = value; }
        }
        private List<int> availableEventIDs;
        public List<int> AvailableEventIDs
        {
            get { return availableEventIDs; }
            set { availableEventIDs = value; }
        } 
        #endregion

        #region Event sources
        private bool showAllSources = true;
        public bool ShowAllSources
        {
            get { return showAllSources; }
            set { showAllSources = value; }
        }
        private List<string> sourceFilters = new List<string>();
        public List<string> SourceFilters
        {
            get { return sourceFilters; }
            set { sourceFilters = value; }
        }
        private List<string> availableSources;
        public List<string> AvailableSources
        {
            get { return availableSources; }
            set { availableSources = value; }
        } 
        #endregion

        #region Machine/logs
        private bool showAllMachineLogs = true;
        public bool ShowAllMachineLogs
        {
            get { return showAllMachineLogs; }
            set { showAllMachineLogs = value; }
        }
        private List<MachineLogEntry> machineLogFilters = new List<MachineLogEntry>();
        public List<MachineLogEntry> MachineLogFilters
        {
            get { return machineLogFilters; }
            set { machineLogFilters = value; }
        }
        private List<MachineLogEntry> availableMachineLog;
        public List<MachineLogEntry> AvailableMachineLog
        {
            get { return availableMachineLog; }
            set { availableMachineLog = value; }
        }
        private bool viewDisabledMachineLogs;
        public bool ViewDisabledMachineLogs
        {
            get { return viewDisabledMachineLogs; }
            set { viewDisabledMachineLogs = value; }
        }
        #endregion

        #endregion

        #region Test connection
        public int TestConnection()
        {
            string sql = "GetEventlogRowCount";
            return (int)GetSingleValue(sql);
        }
        #endregion

        #region Stats - note - currently using direct sql text - investigate convert to sproc
        public List<PeriodCount> GetCountsPerPeiodHistoric(
                CountsPerPeriodType countsPerPeriod,
                EventLogEntryTypes eventLogEntryTypes,
                DateTime from, DateTime to,
                List<MachineLogEntry> logs,
                List<int> eventIDs,
                List<string> sources
            )
        {
            List<PeriodCount> results = new List<PeriodCount>();
            string groupby;
            string where = "";
            string sql = "select ";
            switch (countsPerPeriod)
            {
                case CountsPerPeriodType.Total:
                    groupby = "";
                    break;
                case CountsPerPeriodType.Minute:
                    groupby = "[Year], [Month], [Day], [Hour] ";
                    break;
                case CountsPerPeriodType.Hour:
                    groupby = "[Year], [Month], [Day], [Hour] ";
                    break;
                case CountsPerPeriodType.Day:
                    groupby = "[Year], [Month], [Day] ";
                    break;
                case CountsPerPeriodType.Week:
                    groupby = "[Year], DatePart(wk, cast(cast([Year] as char(4)) + '-' + cast([Month] as char(2)) + '-' + cast([Day] as char(2)) as datetime)) ";
                    break;
                case CountsPerPeriodType.Month:
                    groupby = "[Year], [Month] ";
                    break;
                default:
                    groupby = "";
                    break;
            }
            where = "WHERE cast(cast([Year] as char(4)) + '-' + cast([Month] as char(2)) + '-' + cast([Day] as char(2)) as datetime) between '" + from.ToString("yyyy-MM-dd HH:mm") + "' and '" + to.ToString("yyyy-MM-dd HH:mm") + "' ";
            switch (eventLogEntryTypes)
            {
                case EventLogEntryTypes.Errors:
                    where += " and (EventTypeName like 'E%') ";
                    break;
                case EventLogEntryTypes.Warnings:
                    where += " and (EventTypeName like 'W%') ";
                    break;
                case EventLogEntryTypes.ErrorsAndWarnings:
                    where += " and ((EventTypeName like 'E%') or (EventTypeName like 'W%')) ";
                    break;
                case EventLogEntryTypes.Information:
                    where += " and (EventTypeName like 'I%') ";
                    break;
                default:
                    break;
            }
            if (logs.Count > 0)
            {
                where += " and (";
                foreach (MachineLogEntry mle in logs)
                {
                    where += " (m.Machine like '" + mle.Machine.Replace("'", "''") + "' and m.LogName like '" + mle.LogName + "') or ";
                }
                where = where.Trim('o', 'r', ' ') + ") ";
            }
            if (eventIDs.Count > 0)
            {
                where += " and EventID in (";
                foreach (int eventId in eventIDs)
                {
                    where += eventId.ToString() + ",";
                }
                where = where.Trim(',') + ") ";
            }
            if (sources.Count > 0)
            {
                where += " and s.Source in (";
                foreach (string source in sources)
                {
                    where += "'" + source.Replace("'", "''") + "',";
                }
                where = where.Trim(',') + ") ";
            }
            if (groupby.Length > 0)
            {
                sql += groupby + ", sum(Records) FROM MachineLogs m inner join EventLogHistory h on m.MachineLogID = h.MachineLogID inner join EventSources s on h.SourceID = s.SourceID " + where;
                sql += "GROUP BY " + groupby;
                sql += "ORDER BY " + groupby;
            }
            else
                sql += " sum(Records) FROM MachineLogs m inner join EventLogHistory h on m.MachineLogID = h.MachineLogID inner join EventSources s on h.SourceID = s.SourceID " + where;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmnd = new SqlCommand(sql, conn))
                {
                    cmnd.CommandTimeout = CommandTimeout;
                    using (SqlDataReader dr = cmnd.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            PeriodCount periodCount;

                            switch (countsPerPeriod)
                            {
                                case CountsPerPeriodType.Total:
                                    periodCount.PeriodStart = from;
                                    break;
                                case CountsPerPeriodType.Minute:
                                    periodCount.PeriodStart = new DateTime((int)dr[0], byte.Parse(dr[1].ToString()), byte.Parse(dr[2].ToString()), byte.Parse(dr[3].ToString()), 0, 0);
                                    break;
                                case CountsPerPeriodType.Hour:
                                    periodCount.PeriodStart = new DateTime((int)dr[0], byte.Parse(dr[1].ToString()), byte.Parse(dr[2].ToString()), byte.Parse(dr[3].ToString()), 0, 0);
                                    break;
                                case CountsPerPeriodType.Day:
                                    periodCount.PeriodStart = new DateTime((int)dr[0], byte.Parse(dr[1].ToString()), byte.Parse(dr[2].ToString()));
                                    break;
                                case CountsPerPeriodType.Week:
                                    periodCount.PeriodStart = new DateTime((int)dr[0], 1, 1);
                                    periodCount.PeriodStart = periodCount.PeriodStart.AddDays(7 * byte.Parse(dr[1].ToString()));
                                    break;
                                case CountsPerPeriodType.Month:
                                    periodCount.PeriodStart = new DateTime((int)dr[0], byte.Parse(dr[1].ToString()), 1);
                                    break;
                                default:
                                    periodCount.PeriodStart = from;
                                    break;
                            }
                            if (dr[dr.FieldCount - 1] != System.DBNull.Value)
                                periodCount.Count = (int)dr[dr.FieldCount - 1];
                            else
                                periodCount.Count = 0;
                            results.Add(periodCount);
                        }
                    }
                }
            }

            return results;
        }
        public List<PeriodCount> GetCountsPerPeiod(
                CountsPerPeriodType countsPerPeriod,
                EventLogEntryTypes eventLogEntryTypes,
                DateTime from, DateTime to,
                List<MachineLogEntry> logs,
                List<int> eventIDs,
                List<string> sources
            )
        {
            List<PeriodCount> results = new List<PeriodCount>();
            string groupby;
            string where = "";
            string sql = "select ";
            switch (countsPerPeriod)
            {
                case CountsPerPeriodType.Total:
                    groupby = "";
                    break;
                case CountsPerPeriodType.Minute:
                    groupby = "Year(TimeGenerated), Month(TimeGenerated), Day(TimeGenerated), DatePart(hh, TimeGenerated), DatePart(mi, TimeGenerated) ";
                    break;
                case CountsPerPeriodType.Hour:
                    groupby = "Year(TimeGenerated), Month(TimeGenerated), Day(TimeGenerated), DatePart(hh, TimeGenerated) ";
                    break;
                case CountsPerPeriodType.Day:
                    groupby = "Year(TimeGenerated), Month(TimeGenerated), Day(TimeGenerated) ";
                    break;
                case CountsPerPeriodType.Week:
                    groupby = "Year(TimeGenerated), DatePart(wk, TimeGenerated) ";
                    break;
                case CountsPerPeriodType.Month:
                    groupby = "Year(TimeGenerated), Month(TimeGenerated) ";
                    break;
                default:
                    groupby = "";
                    break;
            }
            where = "WHERE TimeGenerated between '" + from.ToString("yyyy-MM-dd HH:mm") + "' and '" + to.ToString("yyyy-MM-dd HH:mm") + "' ";
            switch (eventLogEntryTypes)
            {
                case EventLogEntryTypes.Errors:
                    where += " and (EventTypeName like 'E%') ";
                    break;
                case EventLogEntryTypes.Warnings:
                    where += " and (EventTypeName like 'W%') ";
                    break;
                case EventLogEntryTypes.ErrorsAndWarnings:
                    where += " and ((EventTypeName like 'E%') or (EventTypeName like 'W%')) ";
                    break;
                case EventLogEntryTypes.Information:
                    where += " and (EventTypeName like 'I%') ";
                    break;
                default:
                    break;
            }
            if (logs.Count > 0)
            {
                where += " and (";
                foreach (MachineLogEntry mle in logs)
                {
                    where += " (ComputerName like '" + mle.Machine.Replace("'", "''") + "' and EventLog like '" + mle.LogName + "') or ";
                }
                where = where.Trim('o', 'r', ' ') + ") ";
            }
            if (eventIDs.Count > 0)
            {
                where += " and EventID in (";
                foreach (int eventId in eventIDs)
                {
                    where += eventId.ToString() + ",";
                }
                where = where.Trim(',') + ") ";
            }
            if (sources.Count > 0)
            {
                where += " and SourceName in (";
                foreach (string source in sources)
                {
                    where += "'" + source.Replace("'", "''") + "',";
                }
                where = where.Trim(',') + ") ";
            }

            if (groupby.Length > 0)
            {
                sql += groupby + ", count(*) FROM EventLog " + where;
                sql += "GROUP BY " + groupby;
                sql += "ORDER BY " + groupby;
            }
            else
                sql += " count(*) FROM EventLog " + where;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmnd = new SqlCommand(sql, conn))
                {
                    ;
                    cmnd.CommandTimeout = CommandTimeout;
                    using (SqlDataReader dr = cmnd.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            PeriodCount periodCount;

                            switch (countsPerPeriod)
                            {
                                case CountsPerPeriodType.Total:
                                    periodCount.PeriodStart = from;
                                    break;
                                case CountsPerPeriodType.Minute:
                                    periodCount.PeriodStart = new DateTime((int)dr[0], (int)dr[1], (int)dr[2], (int)dr[3], (int)dr[4], 0);
                                    break;
                                case CountsPerPeriodType.Hour:
                                    periodCount.PeriodStart = new DateTime((int)dr[0], (int)dr[1], (int)dr[2], (int)dr[3], 0, 0);
                                    break;
                                case CountsPerPeriodType.Day:
                                    periodCount.PeriodStart = new DateTime((int)dr[0], (int)dr[1], (int)dr[2]);
                                    break;
                                case CountsPerPeriodType.Week:
                                    periodCount.PeriodStart = new DateTime((int)dr[0], 1, 1);
                                    periodCount.PeriodStart = periodCount.PeriodStart.AddDays(7 * (int)dr[1]);
                                    break;
                                case CountsPerPeriodType.Month:
                                    periodCount.PeriodStart = new DateTime((int)dr[0], (int)dr[1], 1);
                                    break;
                                default:
                                    periodCount.PeriodStart = from;
                                    break;
                            }
                            periodCount.Count = (int)dr[dr.FieldCount - 1];
                            results.Add(periodCount);
                        }
                    }
                }
            }

            return results;
        }
        #endregion

        #region Get lists for log, event id and source filters
        public List<MachineLogEntry> GetMachineLogList(bool viewDisabledLogs)
        {
            List<MachineLogEntry> list = new List<MachineLogEntry>();
            availableMachineLog = new List<MachineLogEntry>();
            string sql = "SelectMachineLogList3";
            SqlParameter[] parameters = new SqlParameter[] 
            {
                new SqlParameter("@showDisabled", viewDisabledLogs)
            };
            DataSet dsMle = base.GetDataSet(sql, CommandType.StoredProcedure, parameters);
            if (dsMle.Tables.Count > 0)
            {
                list.AddRange(
                    from DataRow r in dsMle.Tables[0].Rows
                    select new MachineLogEntry(r["Machine"].ToString(), r["LogName"].ToString())
                    {
                        MachineLogID = (int)r["MachineLogID"],
                        Enabled = (bool)r["Enabled"],
                        PollFrequency = (int)r["PollFrequency"],
                        LastPollTime = (DateTime)(r["LastPollTime"] == DBNull.Value ? new DateTime(2000, 1, 1) : r["LastPollTime"])
                    }
                    );
                availableMachineLog.AddRange(
                        from DataRow r in dsMle.Tables[0].Rows
                        select new MachineLogEntry(r["Machine"].ToString(), r["LogName"].ToString())
                        {
                            MachineLogID = (int)r["MachineLogID"],
                            Enabled = (bool)r["Enabled"],
                            PollFrequency = (int)r["PollFrequency"],
                            LastPollTime = (DateTime)(r["LastPollTime"] == DBNull.Value ? new DateTime(2000, 1, 1) : r["LastPollTime"])
                        }
                    );
            }

            return list;
        }
        public List<int> GetEventIDList()
        {
            List<int> list = new List<int>();
            availableEventIDs = new List<int>();
            string sql = "SelectEventIDList";
            DataSet dsSources = base.GetDataSet(sql, CommandType.StoredProcedure);
            if (dsSources.Tables.Count > 0)
            {
                list.AddRange(
                    from DataRow r in dsSources.Tables[0].Rows
                    select (int)r[0]
                    );
                availableEventIDs.AddRange(
                    from DataRow r in dsSources.Tables[0].Rows
                    select (int)r[0]
                    );
            }

            return list;
        }
        public List<string> GetSourceList()
        {
            List<string> list = new List<string>();
            availableSources = new List<string>();
            string sql = "SelectSourceList";
            DataSet dsSources = base.GetDataSet(sql, CommandType.StoredProcedure);
            if (dsSources.Tables.Count > 0)
            {
                list.AddRange(
                    from DataRow r in dsSources.Tables[0].Rows
                    select r[0].ToString()
                    );
                availableSources.AddRange(
                    from DataRow r in dsSources.Tables[0].Rows
                    select r[0].ToString()
                    );
            }

            return list;
        } 
        #endregion

        #region Main get list method
        public List<EventEntry> GetEntries()
        {
            List<EventEntry> entries = new List<EventEntry>();
            string sql = "SelectEventLogEntries2";
            SqlParameter[] parameters = new SqlParameter[9];
            parameters[0] = new SqlParameter("@RowCount", maxCount);
            parameters[1] = new SqlParameter("@FromDate", fromDate);
            parameters[2] = new SqlParameter("@ToDate", untilDate);
            if (eventLogEntryType == EventLogEntryTypes.All)
                parameters[3] = new SqlParameter("@EventTypeName", DBNull.Value);
            else
                parameters[3] = new SqlParameter("@EventTypeName", GetEventLogEntryTypeString(eventLogEntryType));

            parameters[4] = new SqlParameter("@ViewDisabledMachineLogs", viewDisabledMachineLogs ? 1 : 0);
            if (showAllMachineLogs)
                parameters[5] = new SqlParameter("@MachineLogFilter", DBNull.Value);
            else
                parameters[5] = new SqlParameter("@MachineLogFilter", ListToCSV(machineLogFilters));
            if (viewAllEventIDs)
                parameters[6] = new SqlParameter("@EventIdFilter", DBNull.Value);
            else
                parameters[6] = new SqlParameter("@EventIdFilter", ListToCSV(eventIdsVisible));
            if (showAllSources)
                parameters[7] = new SqlParameter("@SourceFilters", DBNull.Value);
            else
                parameters[7] = new SqlParameter("@SourceFilters", ListToCSV(sourceFilters));
            if (messageFilter.Length <= 2)
                parameters[8] = new SqlParameter("@MessageFilter", DBNull.Value);
            else
                parameters[8] = new SqlParameter("@MessageFilter", "%" + messageFilter.Replace("'", "''") + "%");

            DataSet dsEntries = base.GetDataSet(sql, CommandType.StoredProcedure, parameters);
            if (dsEntries.Tables.Count > 0)
            {
                entries.AddRange(
                    from DataRow r in dsEntries.Tables[0].Rows
                    select new EventEntry()
                    {
                        MachineLogId = (int)r["MachineLogID"],
                        MachineName = r["ComputerName"].ToString(),
                        LogName = r["EventLog"].ToString(),
                        RecordNumber = (int)r["RecordNumber"],
                        EventID = (int)r["EventID"],
                        TimeGenerated = (DateTime)r["TimeGenerated"],
                        SourceName = r["SourceName"].ToString(),
                        EventTypeName = GetEventTypeString(r["EventTypeName"].ToString()),
                        Message = r["Message"].ToString(),
                        Category = r["Category"].ToString(),
                        UserName = r["UserName"].ToString(),
                        Repeat = (int)r["Repeat"]
                    }
                    );
            }
            return entries;
        } 
        #endregion

        #region Helper methods
        private string GetEventLogEntryTypeString(EventLogEntryTypes eventLogEntryTypes)
        {
            switch (eventLogEntryType)
            {
                case EventLogEntryTypes.Errors:
                    return "E,F";
                case EventLogEntryTypes.Warnings:
                    return "W";
                case EventLogEntryTypes.ErrorsAndWarnings:
                    return "E,W,F";
                case EventLogEntryTypes.Information:
                    return "I,S";
                default:
                    return "";
            }
        }
        private string ListToCSV(List<int> list)
        {
            string output = string.Empty;
            foreach (int element in list)
            {
                output += element.ToString() + ",";
            }
            output = output.TrimEnd(',');
            return output;
        }
        private string ListToCSV(List<MachineLogEntry> list)
        {
            string output = string.Empty;
            foreach (MachineLogEntry element in list)
            {
                output += element.Machine + '\\' + element.LogName + ",";
            }
            output = output.TrimEnd(',');
            return output;
        }
        private string ListToCSV(List<string> list)
        {
            string output = string.Empty;
            foreach (string element in list)
            {
                output += element + ",";
            }
            output = output.TrimEnd(',');
            return output;
        }
        private string GetEventTypeString(string eventType)
        {
            switch (eventType.Substring(0, 1))
            {
                case "E":
                    return "Error";
                case "W":
                    return "Warning";
                case "F":
                    return "Failure";
                case "S":
                    return "Success";
                default:
                    return "Information";
            }
        }
        #endregion
    }
}
